﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using HRM.HRMServiceProxy;
using System.Data.OleDb;
using System.IO;
using System.Data.Common;
using HRM.Utilities;
using WeifenLuo.WinFormsUI.Docking;

namespace HRM
{
    public partial class frmImportSMSTien : DockContent
    {
        private OpenFileDialog openFileDialog1;
        private frmProgressbar fProgressbar;
        private string _SMSThongBaoPath = "";

        public frmImportSMSTien()
        {
            InitializeComponent();
        }

        private void toolStripButton1_Click(object sender, EventArgs e)
        {
            //return;
            if (openFileDialog1.ShowDialog(this) == DialogResult.OK)
            {
                _SMSThongBaoPath = openFileDialog1.FileName; 
                //DataSet ds = ReadExcelFile.ReadExcelList(_SMSThongBaoPath);
                DataSet ds = new DataSet();

                string strSql = String.Format("SELECT * FROM [{0}$]", GetSheetNames(_SMSThongBaoPath)[0]);
                string strConn = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + _SMSThongBaoPath + "; Extended Properties=Excel 8.0;";
                OleDbDataAdapter myCommand = new OleDbDataAdapter(strSql, strConn);

                try
                {
                    myCommand.Fill(ds);
                }
                catch (Exception)
                {
                    strConn = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" + _SMSThongBaoPath + "; Extended Properties=Excel 8.0;HDR=YES;IMEX=1;";
                    myCommand = new OleDbDataAdapter(strSql, strConn);
                    myCommand.Fill(ds);
                }
                finally
                {
                    strSql = "";
                }

                if (ds != null)
                {
                    foreach (DataRow dr in ds.Tables[0].Rows)
                    {
                        foreach (DataColumn col in ds.Tables[0].Columns)
                        {
                            if( col.DataType == typeof(string))
                                dr[col] = UnicodeConvert.TCVN3ToUnicode(dr[col].ToString());
                        }
                    }
                    dgvUser1.DataSource = ds.Tables[0];
                    btnImport.Enabled = true;
                }
                else
                {
                    btnImport.Enabled = false;
                }
            }
        }

        public static List<string> GetSheetNames(string path)
        {

            List<string> sheets = new List<string>();

            string connectionString = String.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 8.0;HDR=YES;IMEX=1;""", path);

            DbProviderFactory factory = DbProviderFactories.GetFactory("System.Data.OleDb");

            DbConnection connection = factory.CreateConnection();

            connection.ConnectionString = connectionString;

            connection.Open();

            DataTable tbl = connection.GetSchema("Tables");

            connection.Close();

            foreach (DataRow row in tbl.Rows)
            {

                string sheetName = (string)row["TABLE_NAME"];

                if (sheetName.EndsWith("$"))
                {

                    sheetName = sheetName.Substring(0, sheetName.Length - 1);

                }

                sheets.Add(sheetName);

            }

            return sheets;

        }

        void bgJ_DoWork_ImportSMSThongBao(object sender, DoWorkEventArgs e)
        {
            e.Result = ImportSMSThongBao();
        }

        private object ImportSMSThongBao()
        {
            bool retVal = true;
            StringBuilder sbImport ;
            try
            {
                MainServiceClient _wcfMain = Service.MainService();
                DataTable dtImport = ((DataTable)dgvUser1.DataSource);
                
                foreach (DataRow dr in dtImport.Rows)
                {
                    sbImport = new StringBuilder();
                    string sCondition = " WHERE madvi = '" + dr["madvi"].ToString() + "' AND thang='" + dr["thang"].ToString() + "' AND ngay='" + Globals.Object_GetString(dr["ngay"],"MM/dd/yyyy").Replace("'", "''")  + "' AND tien_vnd='" + dr["tien_vnd"].ToString() + "' ";
                    //
                    sbImport.Append(" IF NOT EXISTS( SELECT * FROM HRM_SMS_Tien " + sCondition + ")");
                    sbImport.Append(" INSERT INTO dbo.HRM_SMS_Tien ");
	                sbImport.Append(" VALUES(");
                    for(int idx = 0; idx < dtImport.Columns.Count; idx++)
                    {
                        if (dtImport.Columns[idx].DataType == typeof(string))
                            sbImport.Append(" N'" + Globals.Object_GetString(dr[idx], "").Replace("'","''") + "'");
                        else if (dtImport.Columns[idx].DataType == typeof(double))
                            sbImport.Append(" '" + Globals.Object_GetValue<double>(dr[idx]).ToString().Replace("'", "''") + "'");
                        else if(dtImport.Columns[idx].DataType == typeof(DateTime))
                            sbImport.Append(" '" + Globals.Object_GetString(dr[idx],"MM/dd/yyyy").Replace("'", "''") + "'");
                        else
                            sbImport.Append(" N'" + Globals.Object_GetString(dr[idx], "").Replace("'", "''") + "'");

                        if(idx != dtImport.Columns.Count - 1) 
                            sbImport.Append(" ,");
                    }
                    sbImport.Append(" ) ELSE UPDATE HRM_SMS_Tien SET ");
                    for(int idx = 0; idx < dtImport.Columns.Count; idx++)
                    {
                        if (dtImport.Columns[idx].DataType == typeof(string))
                            sbImport.Append(dtImport.Columns[idx].ColumnName + " = N'" + Globals.Object_GetString(dr[idx], "").Replace("'", "''") + "'");
                        else if (dtImport.Columns[idx].DataType == typeof(double))
                            sbImport.Append(dtImport.Columns[idx].ColumnName + " = '" + Globals.Object_GetValue<double>(dr[idx]).ToString().Replace("'", "''") + "'");
                        else if (dtImport.Columns[idx].DataType == typeof(DateTime))
                            sbImport.Append(dtImport.Columns[idx].ColumnName + " = '" + Globals.Object_GetString(dr[idx], "MM/dd/yyyy").Replace("'", "''") + "'");
                        else
                            sbImport.Append(dtImport.Columns[idx].ColumnName + " = N'" + Globals.Object_GetString(dr[idx], "").Replace("'", "''") + "'");

                        if(idx != dtImport.Columns.Count - 1) 
                            sbImport.Append(" ,");
                    }
	                sbImport.Append(sCondition);

                    //
                    _wcfMain.ExecuteQuery(sbImport.ToString());
                }
            }
            catch (Exception ex)
            {
                retVal = false;
            }
            return retVal;
        }

        void bgJ_RunWorkerCompleted_SMSThongBao(object sender, RunWorkerCompletedEventArgs e)
        {
            if (Convert.ToBoolean(e.Result))
                MessageBox.Show(this, "Hoàn thành Import dữ liệu SMS Thông báo!");
            else
                MessageBox.Show(this, "Import dữ liệu SMS Thông báo bị lỗi!\nHãy thử lại");

            fProgressbar.Close();
        }

        private void btnImport_Click(object sender, EventArgs e)
        {
            BackgroundWorker bgJ = new BackgroundWorker();

            bgJ.DoWork += new DoWorkEventHandler(bgJ_DoWork_ImportSMSThongBao);

            bgJ.RunWorkerCompleted += new RunWorkerCompletedEventHandler(bgJ_RunWorkerCompleted_SMSThongBao);

            bgJ.RunWorkerAsync();

            fProgressbar = new frmProgressbar();
            fProgressbar.Show(this);
        }
    }
}
